package es.usj.redes.dao;

import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

import es.usj.redes.dto.Users;
/**
 * Clase que implementa el acceso a la tabla Users para JDBC.
 * <p>
 * En esta clase se encapsulan los metodos necesarios para trabajar con la 
 * tabla Users, la cual esta descrita en el Bean {@link es.usj.redes.dto.User user}
 * <br/>
 * Implementa la interfaz {@link es.usj.redes.dao.UsersDAO UsersDAO} para dotar a la clase de robustez 
 * empleando el patron DAOFactory.
 * En esta clase se especifica el acceso a la BBDD para la tabla enviados mediante 
 * JDBC, creando en cada metodo un objeto {@link java.sql.Connection} con los 
 * datos especificados en {@link es.usj.redes.dao.DataSource} para acceder.
 * 
 * Son metodos CRUD (Create, Read, Update, Delete) tipicos de acceso a Bases de datos.
 * Donde en cada uno tiene la misma estructura, crear el objeto Connection, el resultSet
 * el prepareStatement y a partir de la SQL de cada metodo obtener o introducir (en caso 
 * de update) el valor correspondiente y finalmente devolver si se lista, 
 * en caso contrario no devuelve nada la BBDD, el objeto correspondiente.
 * @version 1.2
 * @since 1.0
 *
 */
public class UsersJdbcDAO implements UsersDAO {
	public void create(Users obj) throws DAOException {
		Connection c = null;
		ResultSet result = null;
		PreparedStatement statement = null;
		try {
			int i = 1;
			c = DataSource.getConnection();
			statement = c
					.prepareStatement("insert into Users (nombre, password) values (?, ?)");
			statement.setString(i++, obj.getNombre());
			statement.setString(i++, obj.getPassword());
			int n = statement.executeUpdate();
			if (n == 0)
				throw new DAOException("No rows updated");
			result = statement.getGeneratedKeys();
			if (!result.next())
				throw new DAOException("No generated keys");
			i = 1;
			obj.setIdUsuario(result.getInt(i++));
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			if (result != null)
				try {
					result.close();
				} catch (SQLException ignored) {
				}
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException ignored) {
				}
			if (c != null)
				try {
					c.close();
				} catch (SQLException ignored) {
				}
		}
	}

	public void delete(int idUsuario) throws DAOException {
		Connection c = null;
		PreparedStatement statement = null;
		try {
			int i = 1;
			c = DataSource.getConnection();
			statement = c
					.prepareStatement("delete from Users where idusuario=?");
			statement.setInt(i++, idUsuario);
			int n = statement.executeUpdate();
			if (n == 0)
				throw new DAOException("No rows updated");
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException ignored) {
				}
			if (c != null)
				try {
					c.close();
				} catch (SQLException ignored) {
				}
		}
	}

	public void update(Users obj) throws DAOException {
		Connection c = null;
		PreparedStatement statement = null;
		try {
			int i = 1;
			c = DataSource.getConnection();
			StringBuffer sb = new StringBuffer(
					"update users set nombre=?, password=? where idusuario=?");

			statement = c.prepareStatement(sb.toString());
			statement.setString(i++, obj.getNombre());
			statement.setString(i++, obj.getPassword());
			statement.setInt(i++, obj.getIdUsuario());
			int n = statement.executeUpdate();
			if (n == 0)
				throw new DAOException("No rows updated");
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException ignored) {
				}
			if (c != null)
				try {
					c.close();
				} catch (SQLException ignored) {
				}
		}
	}

	public Users read(int idUsuario) throws DAOException {
		Connection c = null;
		PreparedStatement statement = null;
		ResultSet result = null;
		try {
			int i = 1;
			c = DataSource.getConnection();
			statement = c
					.prepareStatement(
							"select idusuario, nombre, password from Users where idusuario=?",
							ResultSet.TYPE_SCROLL_INSENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			statement.setInt(i++, idUsuario);
			result = statement.executeQuery();
			Users obj = null;
			if (result.next()) {
				i = 1;
				obj = new Users();
				obj.setIdUsuario(result.getInt(i++));
				obj.setNombre(result.getString(i++));
				obj.setPassword(result.getString(i++));
				return obj;
			} else {
				throw new DAOException("No such instance exception");
			}
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			if (result != null)
				try {
					result.close();
				} catch (SQLException ignored) {
				}
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException ignored) {
				}
			if (c != null)
				try {
					c.close();
				} catch (SQLException ignored) {
				}
		}
	}
	public Users read(String username) throws DAOException {
		Connection c = null;
		PreparedStatement statement = null;
		ResultSet result = null;
		try {
			int i = 1;
			c = DataSource.getConnection();
			statement = c
					.prepareStatement(
							"select idusuario, nombre, password from users where nombre=?",
							ResultSet.TYPE_SCROLL_INSENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			statement.setString(i++, username);
			System.out.println("SQL="+statement.toString()+"###");
			result = statement.executeQuery();
			Users obj = null;
			if (result.next()) {
				i = 1;
				obj = new Users();
				obj.setIdUsuario(result.getInt(i++));
				obj.setNombre(result.getString(i++));
				obj.setPassword(result.getString(i++));
				return obj;
			} else {
				return null;
			}
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			if (result != null)
				try {
					result.close();
				} catch (SQLException ignored) {
				}
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException ignored) {
				}
			if (c != null)
				try {
					c.close();
				} catch (SQLException ignored) {
				}
		}
	}
	public List<Users> listAll() throws DAOException {
		Connection c = null;
		PreparedStatement statement = null;
		ResultSet result = null;
		List list = new ArrayList<Users>();
		try {
			int i;
			c = DataSource.getConnection();
			statement = c
					.prepareStatement("select idusuario, nombre, password from Users");
			result = statement.executeQuery();
			Users obj = null;
			while (result.next()) {
				i = 1;
				obj = new Users();
				obj.setIdUsuario(result.getInt(i++));
				obj.setNombre(result.getString(i++));
				obj.setPassword(result.getString(i++));

				list.add(obj);
			}
			return list;
		} catch (SQLException sqle) {
			throw new DAOException(sqle);
		} finally {
			if (result != null)
				try {
					result.close();
				} catch (SQLException ignored) {
				}
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException ignored) {
				}
			if (c != null)
				try {
					c.close();
				} catch (SQLException ignored) {
				}
		}
	}

}
